package com.grab.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * @author YanShuang
 * @version v_1.0.1
 * @since 2021/4/6 9:51
 */
public class b_init_code_table {

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1、注册JDBC驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2、获取数据库连接
        Connection connection = DriverManager.getConnection(JdbcConstant.DB_URL, JdbcConstant.USER, JdbcConstant.PASS);
        //3、操作数据库
        //获取操作数据库的对象
        Statement statement = connection.createStatement();

//        update china_region_code set is_user = 0;
//
//        update china_region_code , system_area set areaid = area_id, pareaid = 0, is_user = IF(is_user_center is null, 1, is_user_center) where rank = 1 and type = 1 and is_enabled = 1 and area_name = name and 0 = parent_id;
//        update china_region_code a, china_region_code b set a.pareaid = b.areaid where a.pid = b.id and a.type = 2 and b.type = 1;
//        update china_region_code , system_area set areaid = area_id, is_user = IF(is_user_center is null, 1, is_user_center) where rank = 2 and type = 2 and is_enabled = 1 and area_name = name and pareaid = parent_id;
//        update china_region_code a, china_region_code b set a.pareaid = b.areaid where a.pid = b.id and a.type = 3 and b.type = 2;
//        update china_region_code , system_area set areaid = area_id, is_user = IF(is_user_center is null, 1, is_user_center) where rank = 3 and type = 3 and is_enabled = 1 and area_name = name and pareaid = parent_id;
//        update china_region_code a, china_region_code b set a.pareaid = b.areaid where a.pid = b.id and a.type = 4 and b.type = 3;
//        update china_region_code , system_area set areaid = area_id, is_user = IF(is_user_center is null, 1, is_user_center) where rank = 4 and type = 4 and is_enabled = 1 and area_name = name and pareaid = parent_id;
//
//        set @mycnt = (select MAX(area_id) from system_area);
//        UPDATE china_region_code set areaid = (@mycnt := @mycnt + 1) where areaid is null;
//
//        update china_region_code a, china_region_code b set a.pareaid = b.areaid where a.pid = b.id and a.type = 2 and b.type = 1;
//        update china_region_code a, china_region_code b set a.pareaid = b.areaid where a.pid = b.id and a.type = 3 and b.type = 2;
//        update china_region_code a, china_region_code b set a.pareaid = b.areaid where a.pid = b.id and a.type = 4 and b.type = 3;
//
//        update system_area set area_code = null, area_full_code = null;
//        update system_area, china_region_code set area_code = id, area_full_code = code where area_id = areaid;
//        update system_area set area_code = 0, area_full_code = 0 where area_id = 0;
//        update system_area set area_full_code = CONCAT(area_full_code,'0000') where rank = 1 and area_id != 0;
//
//        update system_area set is_enabled=0 where area_code is null;
//
//        INSERT INTO system_area (area_id,	area_code,	area_name,	area_full_code,	area_full_name,	rank,	parent_id,	order,	area_name_spell,	area_name_acronym,	tel_area_code,	axis,	longitude,	latitude,	region_code,	region_name,	is_end,	is_enabled,	effective_start_date,	effective_end_date,	creation_date,	last_update_date,	remarks,	description,	is_user_center)
//        SELECT areaid, id, name,code, '', type, pareaid, areaid, '','','','','','','','', case when type = 3 then 0 when type = 4 then 1 end, 1, null,null, NOW(), NOW(), '', '国家统计局', 0 from china_region_code where areaid not in (select area_id from system_area where is_enabled = 1);
//
//        update system_area a, system_area b, system_area c set a.area_full_name = CONCAT_WS("/",c.area_name,b.area_name,a.area_name),a.axis = CONCAT('$',c.area_id,'$', b.area_id, '$', a.area_id),a.region_code = b.region_code, a.region_name = b.region_name
//        where a.rank = 3 and (a.area_full_name = '' or a.area_full_name is null) and a.parent_id = b.area_id and b.parent_id = c.area_id;
//
//        update system_area a, system_area b, system_area c,system_area d set a.area_full_name = CONCAT_WS("/",d.area_name, c.area_name,b.area_name,a.area_name),a.axis = CONCAT('$',d.area_id, '$',c.area_id,'$', b.area_id, '$', a.area_id),a.region_code = b.region_code, a.region_name = b.region_name
//        where a.rank = 4 and (a.area_full_name = '' or a.area_full_name is null) and a.parent_id = b.area_id and b.parent_id = c.area_id and c.parent_id = d.area_id;
//
//        update system_area a, system_area b set a.region_code = b.region_code, a.region_name = b.region_name where a.parent_id = b.area_id and a.rank = 4;

        //更新areaid和parentid，is_user
        //更新省级areaid和parentid
        String sql = "update china_region_code , system_area set areaid = area_id, pareaid = 0 where rank = 1 and type = 1 and is_enabled = 1 and area_name = `name` and 0 = parent_id;";
        //执行sql，获取结果集
        statement.executeUpdate(sql);

        //更新市级
        sql = "update china_region_code , system_area set areaid = area_id, pareaid = parent_id where rank = 2 and type = 2 and is_enabled = 1 and area_name = `name` and pareaid = parent_id;";
        //执行sql，获取结果集
        statement.executeUpdate(sql);
        //更新县级
        //更新市级
        sql = "update china_region_code , system_area set areaid = area_id, pareaid = parent_id where rank = 3 and type = 3 and is_enabled = 1 and area_name = `name` and pareaid = parent_id;";
        //执行sql，获取结果集
        statement.executeUpdate(sql);
        //更新乡镇级
        //更新市级
        sql = "update china_region_code , system_area set areaid = area_id, pareaid = parent_id where rank = 4 and type = 4 and is_enabled = 1 and area_name = `name` and pareaid = parent_id;";
        //执行sql，获取结果集
        statement.executeUpdate(sql);
        //更新is_user
        sql = "update china_region_code set is_user = 0 where areaid is null or areaid = '';";
        //执行sql，获取结果集
        statement.executeUpdate(sql);


    }
}
